import pymysql


# 定义 数据库工具类
class DBTools(object):
    @classmethod
    def __create_conn(cls):
        # 创建连接
        conn = pymysql.connect(host="116.62.53.189", port=3306, user="root", password="root", database="jiuchao_test2",
                               charset="utf8")
        return conn

    # 查一条记录
    @classmethod
    def query_one(cls, sql):
        cursor = None
        conn = None
        res = None
        try:
            # 创建连接
            conn = DBTools.__create_conn()
            # 创建游标
            cursor = conn.cursor()
            # 执行查询sql
            cursor.execute(sql)
            # 提取结果集，打印
            res = cursor.fetchone()
        except Exception as err:
            print("执行查询sql失败:", str(err))
        finally:
            # 关闭游标
            cursor.close()
            # 关闭连接
            conn.close()
            # 返回查询结果，如果在创建游标出错了，那么回滚，也就没有执行提取结果res,也就是res就是None,返回空
            return res

    # 查增删改记录
    @classmethod
    def db_uid(cls, sql):
        conn = None
        cursor = None
        try:
            # 创建连接
            conn = DBTools.__create_conn()
            # 创建游标
            cursor = conn.cursor()
            # 执行增删改sql
            cursor.execute(sql)
            # 受影响的行
            res = conn.affected_rows()
            print("Affected_rows:", res)
            # 提交事务
            conn.commit()
        except Exception as err:
            print("执行增删改失败:", str(err))
            # 回滚事务
            conn.rollback()
        finally:
            # 关闭游标
            cursor.close()
            # 关闭连接
            conn.close()


if __name__ == '__main__':
    result = DBTools.query_one("select version();")
    print("查询结果：", result)